const fs = require('fs');
const path = require('path');
const initSqlJs = require('sql.js');

// 数据库文件路径
const dbPath = path.join(__dirname, '../server/warehouse.db');

async function simpleAuditSetup() {
  try {
    const SQL = await initSqlJs();
    
    // 检查数据库文件是否存在
    if (!fs.existsSync(dbPath)) {
      console.log('❌ 数据库文件不存在:', dbPath);
      return;
    }
    
    // 从文件加载数据库
    const fileBuffer = fs.readFileSync(dbPath);
    const db = new SQL.Database(fileBuffer);
    console.log('✅ 数据库已加载');
    
    // 创建审计表
    const createTableSQL = `
      CREATE TABLE IF NOT EXISTS tasklist_audit (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        task_id INTEGER NOT NULL,
        old_is_issued INTEGER,
        new_is_issued INTEGER,
        change_type TEXT NOT NULL,
        changed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        changed_by TEXT
      )
    `;
    
    try {
      db.run(createTableSQL);
      console.log('✅ 审计表创建成功');
    } catch (error) {
      console.error('❌ 审计表创建失败:', error.message);
    }
    
    // 创建更新触发器
    const createUpdateTriggerSQL = `
      CREATE TRIGGER IF NOT EXISTS track_tasklist_is_issued_update
        AFTER UPDATE ON TaskList
        FOR EACH ROW
        WHEN OLD.is_issued != NEW.is_issued
      BEGIN
        INSERT INTO tasklist_audit (
          task_id,
          old_is_issued,
          new_is_issued,
          change_type,
          changed_at,
          changed_by
        )
        VALUES (
          NEW.id,
          OLD.is_issued,
          NEW.is_issued,
          'UPDATE',
          CURRENT_TIMESTAMP,
          'SYSTEM'
        );
      END
    `;
    
    try {
      db.run(createUpdateTriggerSQL);
      console.log('✅ 更新触发器创建成功');
    } catch (error) {
      console.error('❌ 更新触发器创建失败:', error.message);
    }
    
    // 创建插入触发器
    const createInsertTriggerSQL = `
      CREATE TRIGGER IF NOT EXISTS track_tasklist_is_issued_insert
        AFTER INSERT ON TaskList
        FOR EACH ROW
      BEGIN
        INSERT INTO tasklist_audit (
          task_id,
          old_is_issued,
          new_is_issued,
          change_type,
          changed_at,
          changed_by
        )
        VALUES (
          NEW.id,
          NULL,
          NEW.is_issued,
          'INSERT',
          CURRENT_TIMESTAMP,
          'SYSTEM'
        );
      END
    `;
    
    try {
      db.run(createInsertTriggerSQL);
      console.log('✅ 插入触发器创建成功');
    } catch (error) {
      console.error('❌ 插入触发器创建失败:', error.message);
    }
    
    // 保存数据库
    const data = db.export();
    const buffer = Buffer.from(data);
    fs.writeFileSync(dbPath, buffer);
    console.log('💾 数据库已保存');
    
    // 验证设置
    try {
      console.log('🔍 验证审计设置...');
      
      // 检查审计表
      const tableCheck = db.prepare("SELECT name FROM sqlite_master WHERE type='table' AND name='tasklist_audit'");
      const tableResult = tableCheck.getAsObject();
      console.log('📊 审计表检查:', tableResult ? '存在' : '不存在');
      
      // 检查触发器
      const triggerCheck = db.prepare("SELECT name FROM sqlite_master WHERE type='trigger' AND name LIKE 'track_tasklist_%'");
      const triggerResults = [];
      while (triggerCheck.step()) {
        triggerResults.push(triggerCheck.getAsObject());
      }
      console.log('🔄 触发器检查:', triggerResults.length > 0 ? `找到 ${triggerResults.length} 个触发器` : '未找到触发器');
      triggerResults.forEach(trigger => {
        console.log(`  - ${trigger.name}`);
      });
      
    } catch (error) {
      console.error('❌ 验证失败:', error.message);
    }
    
    db.close();
    console.log('✅ 审计设置完成');
  } catch (error) {
    console.error('❌ 审计设置失败:', error);
  }
}

// 执行设置
simpleAuditSetup();